﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.Win32;
using System.Configuration;

namespace XLETL.TaskManager.Controls
{
    public partial class DBConnector : UserControl
    {
        //private const string SQLAuthConnPattern = "Data Source={0};Initial Catalog={1};User Id={2};Password={3};Connect Timeout=30;{4}";
        //private const string SQLWinAuthConnPattern = "Data Source={0};Initial Catalog={1};Integrated Security=SSPI;Connect Timeout=30;";
        //private const string SQLExpressAuthConnPattern = "Data Source={0};AttachDbFilename={1};User Id={2};Password={3};Persist Security Info=true;";
        //private const string SQLExpressWinAuthConnPattern = "Data Source={0};AttachDbFilename={1};Integrated Security=True;Connect Timeout=30;User Instance=true";

        private OpenFileDialog openFileDialog = null;
        private Panel currentPanel = null;

        private string _connString = string.Empty;
        public string ConnectionString
        {
            get
            {
                _connString = GetSqlConnectionString(cbDatabaseSources.SelectedIndex);
                return _connString;
            }
            set
            {
                if (value != null)
                {
                    _connString = value;
                }
            }
        }

        public string DatabaseName
        {
            get
            {
                return GetDatabaseName(cbDatabaseSources.SelectedIndex);
            }
        }

        public string DALClassName
        {
            get
            {
                if (cbDatabaseSources.SelectedValue != null)
                    return Convert.ToString(cbDatabaseSources.SelectedValue);
                else return null;
            }
        }

        //private string _serverName = string.Empty;
        /*public string ServerName
        {
            get
            {
                // SQL SERVER Express
                if (cbDatabaseSources.SelectedIndex == 1)
                {
                    return cbSqlExpressName.Text;
                }
                else if (cbDatabaseSources.SelectedIndex == 0)// SQL SERVER
                {
                    return cbSqlServerName.Text;
                }
                else return "";
            }
            set
            {
                // SQL SERVER Express
                if (cbDatabaseSources.SelectedIndex == 1)
                {
                    cbSqlExpressName.Items.Add(value);
                }
                else if (cbDatabaseSources.SelectedIndex == 0)// SQL SERVER
                {
                    cbSqlServerName.Items.Add(value);
                }
            }
        }*/

        public DBConnector()
        {
            InitializeComponent();

            this.currentPanel = this.pnlSqlServer; // must be set as any subsequent call to showpanel method requires currentPanel.Location property

            //cbDatabaseSources.SelectedIndex = 0; // SQL Server 2000 - 2005 default  

            PopulateDBProviders();

            this.cbSqlServerName.Text = "CHOMISKOSIKS\\SQLDEV";
        }

        /// <summary>
        /// Retrieve list of available providers from configuration file and bind it to combobox
        /// </summary>
        private void PopulateDBProviders()
        {
            Dictionary<string, string> providerCol = new Dictionary<string, string>();
            string[] providers = ConfigurationManager.AppSettings.AllKeys;
            //string[] providers = new string[] { "SqlServer", "MySql" };
            //string[] values = new string[] { "XLETL.SQLServerDAL", "XLETL.MySqlDAL" };

            for (int i = 0; i < providers.Length; i++)
            {
                providerCol.Add(providers[i], ConfigurationManager.AppSettings[i]);
                //providerCol.Add(providers[i], values[i]);
            }

            cbDatabaseSources.DisplayMember = "Key";
            cbDatabaseSources.ValueMember = "Value";
            cbDatabaseSources.DataSource = new BindingSource(providerCol, null); 
        }
                

        public void SaveSqlConnectionSettings()
        {
            this.SaveConnectionProperties(currentPanel.Name);
        }

        private void SaveConnectionProperties(string panelName)
        {
            switch (panelName)
            {
                case "pnlSqlServer":
                    WriteEncryptedConnectionStringSection(string.Format("SqlServerConn_{0}", cbSqlServerName.Text.Replace("\\", "_")), GetSqlConnectionString(cbDatabaseSources.SelectedIndex), "System.Data.SqlClient");
                    break;
                case "pnlSqlExpress":
                    WriteEncryptedConnectionStringSection(string.Format("SqlExpressConn_{0}", cbSqlExpressName.Text), GetSqlConnectionString(cbDatabaseSources.SelectedIndex), "System.Data.SqlClient");
                    break;
            }
        }

        private void PopulateFormWithSqlConnProperties(string panelName, ComboBox cbServerName)
        {
            ConnectionStringSettingsCollection cssc = ConfigurationManager.ConnectionStrings;
            SqlConnectionStringBuilder sqlConnSB = null;
            cbServerName.Items.Clear();

            foreach (ConnectionStringSettings conSettings in cssc)
            {
                if (conSettings.Name.StartsWith(panelName.Substring(3), true, System.Globalization.CultureInfo.InvariantCulture))
                {
                    sqlConnSB = new SqlConnectionStringBuilder();
                    sqlConnSB.ConnectionString = conSettings.ConnectionString;
                    cbServerName.Items.Add(sqlConnSB);
                    cbSqlServerName.DisplayMember = "DataSource";

                    //ConnectionStringSettingsCollection filteredCol = new ConnectionStringSettingsCollection();
                    //filteredCol.Add(conSettings);
                }
            }
        }

        private void cbDatabaseSources_SelectedIndexChanged(object sender, EventArgs e)
        {
            // SQL SERVER CE
            if (cbDatabaseSources.SelectedIndex == 2)
            {
                this.ShowPanel(this, pnlSQLCE); // set currentPanel value for displaying correct options
                PopulateFormWithSqlConnProperties(pnlSQLCE.Name, cbSqlCeFile);
            }
            // SQL SERVER Express
            if (cbDatabaseSources.SelectedIndex == 1)
            {
                this.ShowPanel(this, pnlSqlExpress);
                PopulateFormWithSqlConnProperties(pnlSqlExpress.Name, cbSqlExpressName);
            }
            // SQL SERVER
            if (cbDatabaseSources.SelectedIndex == 0)
            {
                this.ShowPanel(this, pnlSqlServer);

                PopulateFormWithSqlConnProperties(pnlSqlServer.Name, cbSqlServerName);
            }

        }

        private void btnOptions_Click(object sender, EventArgs e)
        {
            if (btnOptions.Text == "Options")
            {
                btnOptions.Text = "Hide";
                this.ShowPanel(this, pnlOptions);

                // Now display correct options groupbox
                if (currentPanel.Equals(pnlSQLCE))
                {
                    grbAdvancedOptions.Visible = true;
                    grbNetworkProperties.Visible = false;
                    nudConnTimeout.Value = Convert.ToDecimal(30);
                    nudExecTimeout.Value = Convert.ToDecimal(100);
                    grbAdvancedOptions.Location = grbNetworkProperties.Location;
                }
                else
                {
                    grbAdvancedOptions.Visible = false;
                    grbNetworkProperties.Visible = true;
                    cmbProtocol.SelectedIndex = 0;
                    nudConnTimeout.Value = Convert.ToDecimal(15);
                    nudExecTimeout.Value = Convert.ToDecimal(0);
                }
            }
            else
            {
                btnOptions.Text = "Options";
                this.ShowPanel(this, currentPanel);
            }

        }

        private void ShowPanel(Control control, Panel panel)
        {
            foreach (Control cntrl in control.Controls)
            {
                if (cntrl.GetType().FullName.Equals("System.Windows.Forms.Panel"))
                {
                    if (cntrl.Equals(panel))
                    {
                        cntrl.Visible = true;
                        cntrl.Location = currentPanel.Location;
                        cntrl.BringToFront();
                        if (cntrl.Name != "pnlOptions")
                        {
                            currentPanel = panel;
                        }
                    }
                    else
                    {
                        cntrl.Visible = false;
                    }
                }
            }

            // Process controls recursively.
            // This is required if controls contain other controls
            // (for example, if you use group boxes, or other
            // container controls).
            foreach (Control ctrlChild in control.Controls)
            {
                ShowPanel(ctrlChild, panel);
            }

        }


        #region ////////// SQL Server //////////////////////////

        private void RefreshDataBases()
        {
            List<String> databaseList;

            try
            {
                databaseList = new List<String>();

                this.Cursor = Cursors.WaitCursor;

                this.cbDatabases.Items.Clear();

                string conectionStr = GetSqlConnectionString(cbDatabaseSources.SelectedIndex);

                databaseList = FindSqlServerDataBases(conectionStr);

                // Populate combobox with database list
                foreach (String sBase in databaseList)
                {
                    this.cbDatabases.Items.Add(sBase);
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }

        private List<String> FindSqlServerDataBases(string connString)
        {
            System.Data.SqlClient.SqlConnection osqlConnection;
            System.Data.SqlClient.SqlCommand osqlCommand;
            System.Data.SqlClient.SqlDataReader osqlDataReader;
            List<String> ListBases;

            osqlConnection = new System.Data.SqlClient.SqlConnection();
            osqlCommand = new System.Data.SqlClient.SqlCommand();
            ListBases = new List<String>();

            try
            {
                // Connection
                osqlConnection.ConnectionString = connString;

                osqlConnection.Open();

                // Command
                osqlCommand.Connection = osqlConnection;
                osqlCommand.CommandType = System.Data.CommandType.Text;
                osqlCommand.CommandText = "use master; select name from sys.databases order by name";

                // Execution command
                osqlDataReader = osqlCommand.ExecuteReader();

                while (osqlDataReader.Read())
                {
                    ListBases.Add(osqlDataReader["name"].ToString());

                }

                osqlDataReader.Close();

            }
            catch (Exception)
            {

            }
            finally
            {
                if (osqlConnection != null)
                {
                    if (osqlConnection.State == ConnectionState.Open)
                    {
                        osqlConnection.Close();
                    }
                }
            }

            return ListBases;
        }

        private void rbAuthWinSQLServer_CheckedChanged(object sender, EventArgs e)
        {
            txtSqlServerUserName.Enabled = rbAuthSQLServer.Checked;
            txtSqlServerPassword.Enabled = rbAuthSQLServer.Checked;
            txtSqlServerUserName.Text = "";
            txtSqlServerPassword.Text = "";
            chkSqlServerSavePass.Enabled = rbAuthSQLServer.Checked;
        }

        private void rbAuthSQLServer_CheckedChanged(object sender, EventArgs e)
        {

        }

        #endregion

        #region ///////// SQL Express //////////////////////

        private void btnBrowseFile_Click(object sender, EventArgs e)
        {
            openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Database file Microsoft SQL Server (*.mdf)|*.mdf";

            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                TxtSqlExpressFile.Text = openFileDialog.FileName;
            }
            openFileDialog = null;
        }

        private void rbAuthSQLExpress_CheckedChanged(object sender, EventArgs e)
        {
            txtSqlExpressUserName.Enabled = rbAuthSQLExpress.Checked;
            txtSqlExpressPassword.Enabled = rbAuthSQLExpress.Checked;
        }

        #endregion

        #region /////////// TEST CONNECTION ////////////////////////        

        public void TestSqlServerConnection(int dbSourceIndex)
        {
            using (SqlConnection sqlConn = new SqlConnection())
            {
                try
                {
                    sqlConn.ConnectionString = GetSqlConnectionString(dbSourceIndex);
                    this.Cursor = Cursors.WaitCursor;
                    sqlConn.Open();
                    this.Cursor = Cursors.Default;

                    CodeProject.Dialog.MsgBox.Show("Connection Successful.", "Test Connection", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                }
                catch (Exception ex)
                {
                    this.Cursor = Cursors.Default;
                    CodeProject.Dialog.MsgBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                }
                finally
                {
                    sqlConn.Close();
                }
            }
        }

        private void btnTestConn_Click(object sender, EventArgs e)
        {
            this.TestSqlServerConnection(cbDatabaseSources.SelectedIndex);
        }

        /// <summary>
        /// Get Connection string based on the entered data
        /// </summary>
        /// <param name="dbSourceIndex"></param>
        public string GetSqlConnectionString(int dbSourceIndex)
        {
            SqlConnectionStringBuilder sqlConnSB = new SqlConnectionStringBuilder();

            switch (dbSourceIndex)
            {
                case 0:  // SQL Server           
                    sqlConnSB.DataSource = cbSqlServerName.Text;
                    sqlConnSB.InitialCatalog = cbDatabases.Text;
                    if (chkSqlServerSavePass.Checked)
                    {
                        sqlConnSB.UserID = this.txtSqlServerUserName.Text;
                        sqlConnSB.Password = this.txtSqlServerPassword.Text;
                    }
                    sqlConnSB.IntegratedSecurity = rbAuthWinSQLServer.Checked;

                    break;
                case 1:  // SQL Express
                    sqlConnSB.DataSource = cbSqlExpressName.Text;
                    sqlConnSB.AttachDBFilename = TxtSqlExpressFile.Text;
                    if (chkSqlExpressSavePass.Checked)
                    {
                        sqlConnSB.UserID = this.txtSqlExpressUserName.Text;
                        sqlConnSB.Password = this.txtSqlExpressPassword.Text;
                    }
                    sqlConnSB.IntegratedSecurity = rbAuthWinSQLExpress.Checked;

                    break;
                case 2:

                    break;

            }

            // Common for Sql Server and Express
            sqlConnSB.Encrypt = chbEncryptConn.Checked;
            sqlConnSB.ConnectTimeout = Convert.ToInt32(nudConnTimeout.Value);

            int loadBalanceTimeout = Convert.ToInt32(nudExecTimeout.Value);
            if (loadBalanceTimeout > 0)
            {
                sqlConnSB.Pooling = true;
                sqlConnSB.LoadBalanceTimeout = loadBalanceTimeout;
            }

            if (cmbProtocol.SelectedIndex > 0) // Default == TCP/IP
            {
                string networkLibrary = "";

                switch (cmbProtocol.SelectedIndex)
                {
                    case 1: // Shared memory
                        networkLibrary = "dbmslpcn";
                        break;
                    case 2: // TCP/IP
                        networkLibrary = "dbmssocn";
                        break;
                    case 3: // IPX
                        networkLibrary = "dbmsspxn";
                        break;
                    case 4: // Named pipe
                        networkLibrary = "dbnmpntw";
                        break;
                    case 5: // Multiprotocl
                        networkLibrary = "dbmsrpcn";
                        break;
                }

                sqlConnSB.NetworkLibrary = networkLibrary;
                sqlConnSB.PacketSize = Convert.ToInt32(nudPacketSize.Value);
            }

            return sqlConnSB.ConnectionString;

        }

        private string GetDatabaseName(int dbSourceIndex)
        {
            string dbName = string.Empty;

            switch (dbSourceIndex)
            {
                case 0:  // SQL Server 
                    if (cbDatabases.Text != null)
                    {
                        dbName = cbDatabases.Text;
                    }
                    break;
                case 1:  // SQL Express
                    if (TxtSqlExpressFile.Text != null)
                    {
                        dbName = TxtSqlExpressFile.Text;
                    }
                    break;
                default:
                    break;
            }

            return dbName;
        }

        #endregion

        private void cbDatabases_DropDown(object sender, EventArgs e)
        {
            this.RefreshDataBases();
        }

        private void WriteEncryptedConnectionStringSection(string name, string constring, string provider)
        {
            // Get the configuration file for the current application. Specify
            // the ConfigurationUserLevel.None argument so that we get the
            // configuration settings that apply to all users.
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            // Get the connectionStrings section from the configuration file.
            ConnectionStringsSection section = config.ConnectionStrings;
            // If the connectionString section does not exist, create it.
            if (section == null)
            {
                section = new ConnectionStringsSection();
                config.Sections.Add("connectionSettings", section);
            }
            // If it is not already encrypted, configure the connectionStrings
            // section to be encrypted using the standard RSA Proected
            // Configuration Provider.
            if (!section.SectionInformation.IsProtected)
            {
                // Remove this statement to write the connection string in clear
                // text for the purpose of testing.
                //section.SectionInformation.ProtectSection("RsaProtectedConfigurationProvider");
            }
            // Create a new connection string element and add it to the
            // connection string configuration section.

            // Remove object if exists
            for (int i = 0; i < section.ConnectionStrings.Count; i++)
            {
                if (section.ConnectionStrings[i].Name.Equals(name))
                {
                    section.ConnectionStrings.RemoveAt(i);
                    break;
                }
            }

            ConnectionStringSettings cs = new ConnectionStringSettings(name, constring, provider);
            section.ConnectionStrings.Add(cs);
            // Force the connection string section to be saved.
            section.SectionInformation.ForceSave = true;
            // Save the updated configuration file.
            config.Save(ConfigurationSaveMode.Full);
        }

        private void cbSqlServerName_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnectionStringBuilder scsb = cbSqlServerName.SelectedItem as SqlConnectionStringBuilder;

            if (!scsb.IntegratedSecurity)
            {
                rbAuthSQLServer.Checked = true;
                txtSqlServerUserName.Text = scsb.UserID;
                txtSqlServerPassword.Text = scsb.Password;

                chkSqlServerSavePass.Checked = (scsb.Password == string.Empty || scsb.UserID == string.Empty) ? false : true;

            }
            else
            {
                rbAuthSQLServer.Checked = false;
                rbAuthWinSQLServer.Checked = true;
            }
        }





    }
}
